﻿-- =============================================
-- Author:		Sandy Stewart
-- Create date: 
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[fnAllTitles] 
(
	-- Add the parameters for the function here
	@CPALId int
)
RETURNS varchar(1000)
AS
BEGIN
	-- Declare the return variable here
	Declare @AllTitles varchar(1000);
	
	Declare @AllTitlesTemp TABLE (FullTitle varchar(1000));
	
	-- Add the T-SQL statements to compute the return value here
	Insert into @AllTitlesTemp (FullTitle)
	Select FullTitle
     FROM
			dbo.Positions RIGHT OUTER JOIN 
				dbo.ParliamentarianPositions on Positions.Id = ParliamentarianPositions.Positions_Id
				FULL OUTER JOIN
				dbo.Parliamentarians on ParliamentarianPositions.Parliamentarian_CPALId = Parliamentarians.CPALId
     WHERE dbo.Parliamentarians.CPALId = @CPALId
     ORDER BY dbo.Positions.FullTitle ASC;

	-- special case for treasurer - must appear first in sort order
	If (Select Count(*) from @AllTitlesTemp where FullTitle = 'Treasurer') > 0
	Begin
		Select @AllTitles = coalesce(@AllTitles + ', ', '') + FullTitle
		From @AllTitlesTemp Where FullTitle != 'Treasurer';
		Select @AllTitles = 'Treasurer, ' + @AllTitles;
	end
	Else
	Begin
		Select @AllTitles = coalesce(@AllTitles + ', ', '') + FullTitle
		From @AllTitlesTemp
	End
	
	
	-- Return the result of the function
	RETURN @AllTitles

END